package com.gdk.ksoft.assembly.dbhelper;

import android.database.Cursor;
import android.text.TextUtils;

import com.lidroid.xutils.DbUtils;
import com.lidroid.xutils.db.sqlite.SqlInfo;
import com.lidroid.xutils.db.table.DbModel;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Created by hel on 2014-12-09.
 * 报表数据查询辅助类
 */
public class FreporterDbHelper {

    //-----------------------------查找指定日期的【订单/销售单】记录-------------------------------------

    /**
     * 根据日期查询【订单/销售单】日报表
     * @param date
     * @param type 0 - 销售单  1 - 订单
     * @return HashMap  参数值：  xid   销售单ID
     *                           kmc   客户名称
     *                           kico  客户地图图标
     *                           kadd  客户送货地址
     *                           xdjh  单据号
     *                           xssl  销售数量
     *                           xsss  销售散数
     *                           xssy  销售金额
     *                           xsdw  销售单位
     *                           hsdw  核算单位
     *                           djbz  单机标识 0-单机 1-网络
     * @throws Exception
     */
    public static List<HashMap<String,Object>> findAllOrderStatments(final DbUtils dbUtils,String date,int type)throws Exception{
        List<HashMap<String,Object>> lst = new ArrayList<>();
        String sql = String.format("SELECT x.id,k.mc,l.dttb,d.shdz,x.djh,xs.sl,xs.ss,xs.sy,xs.xsdw,xs.hsdw,x.djbz " +
                      "FROM xiaoshoudan x,kehu k,kehu_shdz d,kehu_lx l," +
                            "(SELECT xsd_id,SUM(s.xsdwsl) sl,SUM(s.xsdwss) ss,SUM(yfje) sy, hsdw,xsdw FROM xiaoshoudan_sp s GROUP BY xsd_id) xs " +
                      "WHERE d.id <> 0 AND k.id = d.kh_id AND d.khlx = l.id AND x.kh_id = k.id AND x.shdz_id = d.id AND xs.xsd_id = x.djh %s;",
                "AND date(xdsj) = date('"+date+"') AND x.ddlx="+type+" ORDER BY x.xdsj DESC;");
        Cursor cursor = dbUtils.execQuery(sql);
        while(cursor.moveToNext()){
            HashMap<String,Object> item = new HashMap<>();
            item.put("xid",cursor.getInt(0));//获取销售单ID
            item.put("kmc",cursor.getString(1));//获取客户名称
            item.put("kico",cursor.getString(2));//获取客户地图图标
            item.put("kaddr",cursor.getString(3));//获取客户送货地址
            item.put("xdjh",cursor.getString(4));//获取单据号
            item.put("xssl",cursor.getInt(5));//获取销售数量
            item.put("xsss",cursor.getInt(6));//获取销售散数
            item.put("xssy",cursor.getDouble(7));//获取销售金额
            item.put("xsdw",cursor.getString(8));//获取销售单位
            item.put("hsdw",cursor.getString(9));//获取核算单位
            item.put("djbz",cursor.getInt(10));//单机标识 0-单机 1-网络
            lst.add(item);
        }
        cursor.close();
        return lst;
    }


    /**
     * 统计某一天的【销售单】情况
     * @param date
     * @return DbModel值: txj     现金总和
     *                    tqk      欠款总和
     *                    tyh      银行金额总和
     *                    tml      抹零数据总和
     *                    tjs      件数之和
     *                    tss      散数之和
     *                    aal/bbl/ccl/dd/  现金张数/欠款张数/银行张数/抹零张数
     *                    tmoney   金额之和
     *                    tl       张数之和
     */
    public static DbModel makeAccountByDate(final DbUtils dbUtils,final String date)throws Exception{
        SqlInfo sqlInfo = new SqlInfo("SELECT IFNULL(SUM(xjje),0) txj,IFNULL(SUM(qkje),0) tqk,IFNULL(SUM(yhje),0) tyh,IFNULL(SUM(mlje),0) tml,IFNULL(SUM(js),0) tjs,IFNULL(SUM(ss),0) tss,IFNULL(a.al,0) aal,IFNULL(b.bl,0) bbl,IFNULL(c.cl,0) ccl,IFNULL(d.dl,0) ddl," +
                "       IFNULL(SUM(xjje)+SUM(qkje)+SUM(yhje)+SUM(mlje),0) tmoney," +
                "       IFNULL(a.al+b.bl+c.cl+d.dl,0) tl " +
                "FROM xiaoshoudan x,xiaoshoujisuan j," +
                "  (SELECT COUNT(*) al FROM xiaoshoujisuan WHERE xjje<>0 AND date(jsrq) = date('"+date+"')) a," +
                "  (SELECT COUNT(*) bl FROM xiaoshoujisuan WHERE qkje<>0 AND date(jsrq) = date('"+date+"')) b," +
                "  (SELECT COUNT(*) cl FROM xiaoshoujisuan WHERE yhje<>0 AND date(jsrq) = date('"+date+"')) c," +
                "  (SELECT COUNT(*) dl FROM xiaoshoujisuan WHERE mlje<>0 AND date(jsrq) = date('"+date+"')) d " +
                "WHERE x.djh = j.djh AND date(x.fhsj) = date('"+date+"');");
        return dbUtils.findDbModelFirst(sqlInfo);
    }

    /**
     * 查询【订单/销售单】统计信息
     * @param date
     * @param type 0-销售单  1-订单
     * @return  HashMap值：   totalCount  订单总张数
     *                       totalXCount  统计的商品件数散数
     *                       totalMoney   统计的所有订单总金额
     *                       noupload     未上传张数
     */
    public static HashMap<String,Object> findAllOrderTotalInfo(DbUtils dbUtils,String date,int type) throws Exception{
        HashMap<String,Object> item = null;
        String sql = "SELECT COUNT(x.id),IFNULL(SUM(x.js),0),IFNULL(SUM(x.ss),0),IFNULL(SUM(x.sfje),0),IFNULL(noupload,0) " +
                "     FROM xiaoshoudan x,(SELECT COUNT(djbz) noupload FROM xiaoshoudan WHERE date(xdsj) = date('"+date+"') AND djbz = 0"+" AND ddlx = "+type+")" +
                "     WHERE date(x.xdsj) = date('"+date+"') "+" AND x.ddlx = "+type+";";
        Cursor cursor = dbUtils.execQuery(sql);
        if(cursor.moveToFirst()){
            item = new HashMap<>();
            item.put("totalCount",cursor.getInt(0));//统计张数
            item.put("totalXCount",cursor.getInt(1)+"件"+cursor.getInt(2)+"散");
            item.put("totalMoney",cursor.getDouble(3));//总金额
            item.put("noupload",cursor.getInt(4));//未上传张数
        }
        cursor.close();
        return item;
    }

    /**
     * 根据单号查询【订单/销售单】详情
     * @param orderNo
     * @return HashMap值：   xid    销售单ID
     *                       kmc   客户名称
     *                       kico  客户地图图标
     *                       kaddr 客户送货地址
     *                       xdjh  单据号
     *                       xssl  销售件数量
     *                       xsss  销售散数
     *                       xssy  销售金额
     *                       xsdw  销售单位
     *                       hsdw  核算单位
     *                       djbz  机标识 0-单机 1-网络
     *                       kdsj  开单时间
     *                       kinds 商品种类数量
     */
    public static HashMap<String,Object> findOrderDetailByOrderNo(DbUtils dbUtils,String orderNo)throws Exception {
        HashMap<String,Object> data = null;
        String sql = "SELECT x.id,k.mc,l.dttb,d.shdz,x.djh,xs.sl,xs.ss,xs.sy,xs.xsdw,xs.hsdw,x.djbz,x.xdsj,sks.sk " +
                "      FROM xiaoshoudan x,kehu k,kehu_shdz d,kehu_lx l, " +
                "           (SELECT xsd_id,SUM(s.xsdwsl) sl,SUM(s.xsdwss) ss,SUM(yfje) sy, hsdw,xsdw FROM xiaoshoudan_sp s group by xsd_id) xs, " +
                "           (SELECT SUM(kinds) sk FROM(SELECT COUNT(sp_id) kinds FROM xiaoshoudan_sp WHERE xsd_id = '"+orderNo+"' GROUP BY sp_id)) sks " +
                "      WHERE k.id <> 0 AND d.id <> 0 AND k.id = d.kh_id AND d.khlx = l.id AND x.kh_id = k.id AND x.shdz_id = d.id AND xs.xsd_id = x.djh AND x.djh='"+orderNo+"';";
        Cursor cursor = dbUtils.execQuery(sql);
        if(cursor.moveToFirst()){
            data = new HashMap<>();
            data.put("xid",cursor.getInt(0));//获取销售单ID
            data.put("kmc",cursor.getString(1));//获取客户名称
            data.put("kico",cursor.getString(2));//获取客户地图图标
            data.put("kaddr",cursor.getString(3));//获取客户送货地址
            data.put("xdjh",cursor.getString(4));//获取单据号
            data.put("xssl",cursor.getInt(5));//获取销售件数量
            data.put("xsss",cursor.getInt(6));//获取销售散数
            data.put("xssy",cursor.getDouble(7));//获取销售金额
            data.put("xsdw",cursor.getString(8));//获取销售单位
            data.put("hsdw",cursor.getString(9));//获取核算单位
            data.put("djbz", cursor.getInt(10));//单机标识 0-单机 1-网络
            data.put("kdsj",cursor.getString(11));//开单时间
            data.put("kinds",cursor.getInt(12));//获取商品种类数量
        }
        cursor.close();
        return data;
    }

    /**
     * 通过【订单/销售单】号查询其下面的所有商品
     * @param orderNo
     * @return  HashMap值：  gmc     商品名称
     *                      gico    商品图标
     *                      gcode   商品条码
     *                      ghsdw   商品核算单位
     *                      gxsdw   商品销售单位
     *                      gxsdwsl 商品销售单位数量
     *                      gxsdwss 商品销售单位散数
     *                      gyfje   商品应付金额
     *                      gdj     商品标准零售单位价
     *                      glx     商品类型标识
     * @throws Exception
     */
    public static List<HashMap<String,Object>> findAllGoodsByOrderNo(DbUtils dbUtils,String orderNo)throws Exception{
        List<HashMap<String,Object>> list = new ArrayList<>();
        String sql = "SELECT x.mc,s.slt,x.gg,x.tm,x.hsdw,x.xsdw,x.xsdwsl,x.xsdwss,x.yfje,x.bzxsdwj,x.lx  " +
                "    FROM xiaoshoudan_sp x,shangpin s " +
                "    WHERE x.xsd_id = '"+orderNo+"' AND s.id = x.sp_id";
        Cursor cursor = dbUtils.execQuery(sql);
        while(cursor.moveToNext()){
            HashMap<String,Object> item = new HashMap<>();
            item.put("gmc",cursor.getString(0));//商品名称
            item.put("gico",cursor.getString(1));//商品图标
            item.put("ggg",cursor.getString(2)+cursor.getString(4));//商品规格
            item.put("gcode",cursor.getString(3));//商品条码
            item.put("ghsdw",cursor.getString(4));//核算单位
            item.put("gxsdw",cursor.getString(5));//销售单位
            item.put("gxsdwsl",cursor.getInt(6));//销售单位数量
            item.put("gxsdwss",cursor.getInt(7));//销售单位散数
            item.put("gyfje",cursor.getDouble(8));//应付金额
            item.put("gdj",cursor.getDouble(9));//标准零售单位价
            item.put("glx",cursor.getInt(10));//获取商品类型标识
            list.add(item);
        }
        cursor.close();
        return list;
    }

    //====================================查询所有的开单数据==========================================

    /**
     * 查询某个时间段的【所有开单】情况
     * @param startDate
     * @param endDate
     * @return HashMap  参数值：  xid   销售单ID
     *                           kmc   客户名称
     *                           kico  客户地图图标
     *                           kadd  客户送货地址
     *                           xdjh  单据号
     *                           xssl  销售数量
     *                           xsss  销售散数
     *                           xssy  销售金额
     *                           xsdw  销售单位
     *                           hsdw  核算单位
     *                           djbz  单机标识 0-单机 1-网络
     * @throws Exception
     */
    public static List<HashMap<String,Object>> findAllDataInfo(DbUtils dbUtils,String startDate,String endDate)throws Exception{
        String sql = "SELECT x.id,k.mc,l.dttb,d.shdz,x.djh,xs.sl,xs.ss,xs.sy,xs.xsdw,xs.hsdw,x.djbz " +
                    "FROM xiaoshoudan x,kehu k,kehu_shdz d,kehu_lx l," +
                    "(SELECT xsd_id,SUM(s.xsdwsl) sl,SUM(s.xsdwss) ss,SUM(yfje) sy, hsdw,xsdw FROM xiaoshoudan_sp s GROUP BY xsd_id) xs " +
                    "WHERE d.id <> 0 AND k.id = d.kh_id AND d.khlx = l.id AND x.kh_id = k.id AND x.shdz_id = d.id AND xs.xsd_id = x.djh " +
                    "AND (date(xdsj)>=date('"+startDate+"') AND date(xdsj)<=date('"+endDate+"')) ORDER BY x.xdsj DESC;";
        List<HashMap<String,Object>> lst = null;
        Cursor cursor = dbUtils.execQuery(sql);
        while(cursor.moveToNext()){
            if(lst == null) lst = new ArrayList<>();
            HashMap<String,Object> item = new HashMap<>();
            item.put("xid",cursor.getInt(0));//获取销售单ID
            item.put("kmc",cursor.getString(1));//获取客户名称
            item.put("kico",cursor.getString(2));//获取客户地图图标
            item.put("kaddr",cursor.getString(3));//获取客户送货地址
            item.put("xdjh",cursor.getString(4));//获取单据号
            item.put("xssl",cursor.getInt(5));//获取销售数量
            item.put("xsss",cursor.getInt(6));//获取销售散数
            item.put("xssy",cursor.getDouble(7));//获取销售金额
            item.put("xsdw",cursor.getString(8));//获取销售单位
            item.put("hsdw",cursor.getString(9));//获取核算单位
            item.put("djbz",cursor.getInt(10));//单机标识 0-单机 1-网络
            lst.add(item);
        }
        cursor.close();
        return lst;
    }

    /**
     * 查询某个时间段【所有开单】统计信息
     * @param startDate
     * @param endDate
     * @return  HashMap值：   totalCount  订单总张数
     *                       totalXCount  统计的商品件数散数
     *                       totalMoney   统计的所有订单总金额
     *                       noupload     未上传张数
     */
    public static HashMap<String,Object> findAllDateTotalInfo(DbUtils dbUtils,String startDate,String endDate) throws Exception{
        HashMap<String,Object> item = null;
        String sql = "SELECT COUNT(x.id),IFNULL(SUM(x.js),0),IFNULL(SUM(x.ss),0),IFNULL(SUM(x.sfje),0),IFNULL(noupload,0) " +
                "     FROM xiaoshoudan x,(SELECT COUNT(djbz) noupload FROM xiaoshoudan WHERE djbz = 0)" +
                "     WHERE date(x.xdsj) >= date('"+startDate+"') AND date(x.xdsj)<=date('"+endDate+"');";
        Cursor cursor = dbUtils.execQuery(sql);
        if(cursor.moveToFirst()){
            item = new HashMap<>();
            item.put("totalCount",cursor.getInt(0));//统计张数
            item.put("totalXCount",cursor.getInt(1)+"件"+cursor.getInt(2)+"散");
            item.put("totalMoney",cursor.getDouble(3));//总金额
            item.put("noupload",cursor.getInt(4));//未上传张数
        }
        cursor.close();
        return item;
    }

    //-------------------------查找历史【订单/销售单】记录------------------------------------

    /***
     * 查询历史【订单/销售单】记录
     * @param keyword
     * @param startDate
     * @param endDate
     * @param type 0-销售单  1-订单
     * @return HashMap  参数值：  xid   销售单ID
     *                           kmc   客户名称
     *                           kico  客户地图图标
     *                           kadd  客户送货地址
     *                           xdjh  单据号
     *                           xssl  销售数量
     *                           xsss  销售散数
     *                           xssy  销售金额
     *                           xsdw  销售单位
     *                           hsdw  核算单位
     *                           djbz  单机标识 0-单机 1-网络
     * @throws Exception
     */
    public static List<HashMap<String,Object>> findHistoryOrder(final DbUtils dbUtils,String keyword,String startDate,String endDate,int type)throws Exception{
        List<HashMap<String,Object>> list = null;
        String sql="SELECT x.id,k.mc,l.dttb,d.shdz,x.djh,xs.sl,xs.ss,xs.sy,xs.xsdw,xs.hsdw,x.djbz " +
                "                      FROM xiaoshoudan x,kehu k,kehu_shdz d,kehu_lx l,  " +
                "                          (SELECT xsd_id,SUM(s.xsdwsl) sl,SUM(s.xsdwss) ss,SUM(yfje) sy, hsdw,xsdw FROM xiaoshoudan_sp s GROUP BY xsd_id) xs " +
                "                      WHERE date(xdsj) >= date('"+startDate+"') AND date(xdsj)<=date('"+endDate+"') AND k.id = d.kh_id AND d.id <> 0 AND k.id <> 0 " +
                "                           AND d.khlx = l.id AND x.kh_id = k.id AND x.shdz_id = d.id AND xs.xsd_id = x.djh AND x.ddlx = "+type+" ";
        if(!TextUtils.isEmpty(keyword)){
            sql+=" AND k.mc LIKE '%"+keyword+"%' AND k.kjj LIKE '%"+keyword+"%' AND d.shdz LIKE '%"+keyword +"%' AND d.shdh LIKE '%"+keyword+"%' ";
        }
        sql += " ORDER BY x.xdsj DESC;";
        Cursor cursor = dbUtils.execQuery(sql);
        while(cursor.moveToNext()){
            if(list == null) list = new ArrayList<>();
            HashMap<String,Object> item = new HashMap<>();
            item.put("xid",cursor.getInt(0));//获取销售单ID
            item.put("kmc",cursor.getString(1));//获取客户名称
            item.put("kico",cursor.getString(2));//获取客户地图图标
            item.put("kaddr",cursor.getString(3));//获取客户送货地址
            item.put("xdjh",cursor.getString(4));//获取单据号
            item.put("xssl",cursor.getInt(5));//获取销售数量
            item.put("xsss",cursor.getInt(6));//获取销售散数
            item.put("xssy",cursor.getDouble(7));//获取销售金额
            item.put("xsdw",cursor.getString(8));//获取销售单位
            item.put("hsdw",cursor.getString(9));//获取核算单位
            item.put("djbz",cursor.getInt(10));//单机标识 0-单机 1-网络
            list.add(item);
        }
        cursor.close();
        return list;
    }


    //===================================查询历史欠款==================================

    /**
     * 查询所有【历史欠款】信息
     * @param keyword
     * @return HashMap-键值分布
     *         kmc - 客户名称
     *         klxr - 客户联系人
     *         klxrdh - 客户联系人电话
     *         kico - 客户图标
     *         kshdz - 客户收货地址
     *         kshdh - 客户收货电话
     *         kshr - 客户收货人
     *         kdjh - 单据号
     *         kwsje - 未收金额
     *         qcount - 客户欠款天数
     *         qmoney - 客户欠款金额
     * @throws Exception
     */
    public static List<HashMap<String,Object>> findAllHistoryDebt(DbUtils dbUtils,String keyword)throws Exception{
        String sql = "SELECT k.mc,k.lxr,k.dh,l.dttb,z.shdz,z.shdh,z.shr,q.djh,q.wsje " +
                "FROM kehu k,kehu_shdz z,kehu_lx l,kehu_qiankuan q " +
                "WHERE k.id = z.kh_id AND z.khlx = l.id AND q.kh_id = k.id AND q.shdz_id = z.id AND z.id <> 0 AND k.id <> 0 " +
                " AND (k.mc LIKE '%"+keyword+"%' OR k.kjj LIKE '%"+keyword+"%' OR z.shdz LIKE '%"+keyword+"%') ORDER BY q.lrsj;";
        Cursor cursor = dbUtils.execQuery(sql);
        List<HashMap<String,Object>> list = null;
        while(cursor.moveToNext()){
            if(list == null) list = new ArrayList<>();
            HashMap<String,Object> item = new HashMap<>();
            item.put("kmc",cursor.getString(0));//获得客户名称
            item.put("klxr",cursor.getString(1));//获取客户联系人
            item.put("klxrdh",cursor.getString(2));//获取联系人电话
            item.put("kico",cursor.getString(3));//获取客户图标
            item.put("kshdz",cursor.getString(4));//获取收货地址
            item.put("kshdh",cursor.getString(5));//获取收货电话
            item.put("kshr",cursor.getString(6));//获取收货人
            item.put("kdjh",cursor.getString(7));//获取欠款单据号
            item.put("kwsje",cursor.getFloat(8));//获取未收金额
            list.add(item);
        }
        cursor.close();
        return list;
    }


    //===================================查询商品库存信息=====================================

    /**
     * 查询关键字的【商品库存】信息
     * @param keyword
     * @return HashMap - 键值对
     *         gmc   -  商品名称
     *         gpic  -  商品图片
     *         gtm   -  商品规格
     *         gkc   -  商品库存
     *         ggg   -  商品规格
     *         gdw   -  商品单位
     *         gddw  -  商品大单位
     * @throws Exception
     */
    public static List<HashMap<String,Object>> findAllGoodsStockByKeyword(DbUtils dbUtils,final String keyword,boolean isHasStock,final boolean isHasNegative)throws Exception{
        List<HashMap<String,Object>> list = null;
        String sql = "SELECT DISTINCT s.mc,s.slt,s.tm,s.gg,IFNULL((s.kc-s.xl),0) lkc,s.dw,j.jldwmc,IFNULL(j.hsgx,1) " +
                "FROM shangpin s,shangpin_jldw j " +
                "WHERE s.id = j.sp_id AND s.kc-s.xl " + (isHasStock?"<>":"=") + " 0 AND (s.mc LIKE '%"+keyword+"%' OR s.kjj LIKE '%"
                + keyword + "%' OR s.gg LIKE '%" + keyword + "%' OR s.tm LIKE '%" + keyword + "%') "
                + " AND s.kc-s.xl " + (isHasNegative?"<":">") + "0;";
        Cursor cursor = dbUtils.execQuery(sql);
        while(cursor.moveToNext()){
            if(list == null) list = new ArrayList<>();
            HashMap<String,Object> item = new HashMap<>();
            item.put("gmc",cursor.getString(0));//获取商品名称
            item.put("gpic",cursor.getString(1));//获取商品图片
            item.put("gtm",cursor.getString(2));//获取商品条码
            item.put("ggg",cursor.getString(3));//获取商品规格
            item.put("gkc",(int)cursor.getFloat(4));//获取商品库存
            item.put("ghsgx",cursor.getInt(7));//获取换算关系
            item.put("gdw",cursor.getString(5));//获取商品单位
            item.put("gddw",cursor.getString(6));//获取商品的大单位
            list.add(item);
        }
        cursor.close();
        return list;
    }

    /**
     * 查询所有【商品库存】信息
     * @return HashMap - 键值对
     *         gmc   -  商品名称
     *         gpic  -  商品图片
     *         gtm   -  商品规格
     *         gkc   -  商品库存
     *         ggg   -  商品规格
     *         gdw   -  商品单位
     *         gddw  -  商品大单位
     * @throws Exception
     */
    public static List<HashMap<String,Object>> findAllGoodsStock(DbUtils dbUtils,boolean isHasStock,final boolean isHasNegative)throws Exception{
        List<HashMap<String,Object>> list = null;
        String sql = "SELECT DISTINCT s.mc,s.slt,s.tm,s.gg,IFNULL((s.kc-s.xl),0) lkc,s.dw,j.jldwmc,IFNULL(j.hsgx,1) " +
                "FROM shangpin s,shangpin_jldw j " +
                "WHERE s.id = j.sp_id  AND s.kc-s.xl " + (isHasStock?"<>":"=") + " 0 AND s.kc-s.xl " + (isHasNegative?"<":">") + "0;";
        Cursor cursor = dbUtils.execQuery(sql);
        while(cursor.moveToNext()){
            if(list == null) list = new ArrayList<>();
            HashMap<String,Object> item = new HashMap<>();
            item.put("gmc",cursor.getString(0));//获取商品名称
            item.put("gpic",cursor.getString(1));//获取商品图片
            item.put("gtm",cursor.getString(2));//获取商品条码
            item.put("ggg",cursor.getString(3));//获取商品规格
            item.put("gkc",(int)cursor.getFloat(4));//获取商品库存
            item.put("ghsgx",cursor.getInt(7));//获取换算关系
            item.put("gdw",cursor.getString(5));//获取商品单位
            item.put("gddw",cursor.getString(6));//获取商品的大单位
            list.add(item);
        }
        cursor.close();
        return list;
    }

    /**
     * 查询某品牌下的【商品库存】
     * @return HashMap - 键值对
     *         gmc   -  商品名称
     *         gpic  -  商品图片
     *         gtm   -  商品规格
     *         gkc   -  商品库存
     *         ggg   -  商品规格
     *         gdw   -  商品单位
     *         gddw  -  商品大单位
     * @throws Exception
     */
    public static List<HashMap<String,Object>> findAllGoodsStockByBrandId(DbUtils dbUtils,final int brandId,final boolean isHasStock,final boolean isHasNegative)throws Exception{
        List<HashMap<String,Object>> list = null;
        String sql = "SELECT DISTINCT s.mc,s.slt,s.tm,s.gg,IFNULL((s.kc-s.xl),0) lkc,s.dw,j.jldwmc,IFNULL(j.hsgx,1) " +
                "FROM shangpin s,shangpin_jldw j,pinpai p " +
                "WHERE s.id = j.sp_id AND p.id = s.pp_id AND p.id = " + brandId
                + " AND s.kc-s.xl " + (isHasStock?"<>":"=") + " 0 AND s.kc-s.xl " + (isHasNegative?"<":">") + "0;";
        Cursor cursor = dbUtils.execQuery(sql);
        while(cursor.moveToNext()){
            if(list == null) list = new ArrayList<>();
            HashMap<String,Object> item = new HashMap<>();
            item.put("gmc",cursor.getString(0));//获取商品名称
            item.put("gpic",cursor.getString(1));//获取商品图片
            item.put("gtm",cursor.getString(2));//获取商品条码
            item.put("ggg",cursor.getString(3));//获取商品规格
            item.put("gkc",(int)cursor.getFloat(4));//获取商品库存
            item.put("ghsgx",cursor.getInt(7));//获取换算关系
            item.put("gdw",cursor.getString(5));//获取商品单位
            item.put("gddw",cursor.getString(6));//获取商品的大单位
            list.add(item);
        }
        cursor.close();
        return list;
    }

}
